﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Configuration;
using ARR.Entities;
using ARR.DataModel.Repositories;
using System.Data;
using ARR.Entities.Gateways;
using ARR.Shared;
using System.Data.SqlClient;
using System.Transactions;

namespace ARR.Services.Domain.Importers
{
    public class CanadaImporter
    {
        private Country country = null;
        private RangeReview rangeReview = null;

        public CanadaImporter(RangeReview rangeReview)
        {
            this.rangeReview = rangeReview;
        }
        
        
        public Country Country
        {
            get
            {
                if (country == null)
                {
                    var countryRow = CountriesRepository.Select(c => c.Code == Constants.CountryCode.Canada).SingleOrDefault();
                    country = new Country(countryRow);
                }

                return country;
            }
        }
        
        public List<ColumnFormat> CanadaColumnsFormat1
        {
            get
            {
                return new List<ColumnFormat>()
                {
                   new ColumnFormat{ColumnIndex = 0, ColumnName = "Store", ColumnLength = 6},
                   new ColumnFormat{ColumnIndex = 1, ColumnName = "Prov", ColumnLength = 31},
                   new ColumnFormat{ColumnIndex = 2, ColumnName = "cMonth", ColumnLength = 7},
                   new ColumnFormat{ColumnIndex = 3, ColumnName = "UPC", ColumnLength = 26},
                   new ColumnFormat{ColumnIndex = 4, ColumnName = "Description", ColumnLength = 61},
                   new ColumnFormat{ColumnIndex = 5, ColumnName = "Category", ColumnLength = 10},
                   new ColumnFormat{ColumnIndex = 6, ColumnName = "SubCat", ColumnLength = 10},
                   new ColumnFormat{ColumnIndex = 7, ColumnName = "Brand", ColumnLength = 10},
                   new ColumnFormat{ColumnIndex = 8, ColumnName = "Format", ColumnLength = 10},
                   new ColumnFormat{ColumnIndex = 9, ColumnName = "UOM", ColumnLength = 5},
                   new ColumnFormat{ColumnIndex = 10, ColumnName = "Sales", ColumnLength = 41},
                   new ColumnFormat{ColumnIndex = 11, ColumnName = "SKU_Quan", ColumnLength = 41},
                   new ColumnFormat{ColumnIndex = 12, ColumnName = "Sales_Amount", ColumnLength = 41},
                   new ColumnFormat{ColumnIndex = 13, ColumnName = "Cost_Amount", ColumnLength = 41},
                   new ColumnFormat{ColumnIndex = 14, ColumnName = "Margin", ColumnLength = 41, IsLastColumn = true},
                };
            }
        }

        public List<ColumnFormat> CanadaColumnsFormat2
        {
            get
            {
                return new List<ColumnFormat>()
                {
                   new ColumnFormat{ColumnIndex = 0, ColumnName = "Store", ColumnLength = 6},
                   new ColumnFormat{ColumnIndex = 1, ColumnName = "Prov", ColumnLength = 31},
                   new ColumnFormat{ColumnIndex = 2, ColumnName = "Recons_Date", ColumnLength = 12},
                   new ColumnFormat{ColumnIndex = 3, ColumnName = "UPC", ColumnLength = 26},
                   new ColumnFormat{ColumnIndex = 4, ColumnName = "Description", ColumnLength = 61},
                   new ColumnFormat{ColumnIndex = 5, ColumnName = "Category", ColumnLength = 10},
                   new ColumnFormat{ColumnIndex = 6, ColumnName = "SubCat", ColumnLength = 10},
                   new ColumnFormat{ColumnIndex = 7, ColumnName = "Brand", ColumnLength = 10},
                   new ColumnFormat{ColumnIndex = 8, ColumnName = "Format", ColumnLength = 10},
                   new ColumnFormat{ColumnIndex = 9, ColumnName = "UOM", ColumnLength = 5},
                   new ColumnFormat{ColumnIndex = 10, ColumnName = "Sales", ColumnLength = 14},
                   new ColumnFormat{ColumnIndex = 11, ColumnName = "SKU_Quan", ColumnLength = 14},
                   new ColumnFormat{ColumnIndex = 12, ColumnName = "Unit_Ave_Cost", ColumnLength = 26},
                   new ColumnFormat{ColumnIndex = 13, ColumnName = "Sales_Amount", ColumnLength = 18},
                   new ColumnFormat{ColumnIndex = 14, ColumnName = "Cost_Amount", ColumnLength = 26},
                   new ColumnFormat{ColumnIndex = 15, ColumnName = "Margin", ColumnLength = 26, IsLastColumn = true}
                   
                };
            }
        }


        public void ImportArticleMaster()
        {
            IList<ImporterResult> resultsStep1 = null;
            IList<ImporterResult> resultsStep2 = null;
            IList<ImporterResult> resultsStep3 = null;
            IList<ImporterResult> resultsStep4 = null;

            try
            {
                resultsStep1 = ImportArticleMasterStep1();
                WriteTextResults("Step1Results", resultsStep1);

                if (resultsStep1.Count == 0)
                {
                    resultsStep2 = ImportArticleMasterStep2();
                    WriteTextResults("Step2Results", resultsStep2);

                    resultsStep3 = ImportArticleMasterStep3();
                    WriteTextResults("Step3Results", resultsStep3);

                    resultsStep4 = ImportArticleMasterStep4();
                    WriteTextResults("Step4Results", resultsStep4);
                }
            }
            catch (Exception ex)
            {
                string resultsPath = ImporterHelper.GetSalesResultsFilesPath(Country, rangeReview.Title);
                string fullName = Path.Combine(resultsPath, "UnhandledErrors.txt");

                using (StreamWriter sw = new StreamWriter(fullName, true))
                {
                    sw.WriteLine("Exception: ", ex.Message);
                    sw.WriteLine(sw.NewLine);
                    sw.WriteLine("Stack Trace: ", ex.StackTrace);
                }               
            }
        }

        public IList<ImporterResult> ImportArticleMasterStep1()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();

            string fileName = null;
            int? rowNumber = null;
            string line = null;

            int errorCount = 0;
            try
            {
                //Check Prerequisites
                //1. Chequeo que estén los archivos
                string sourceDirectoryPath = ImporterHelper.GetArticleMasterFilePath(Country, rangeReview.Title);
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                if (files.Length == 0)
                {
                    errorCount++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.PreRequisites,
                        Description = "There are no Sales files to import in the server directory.",
                        ErrorType = ErrorType.Error,
                        Id = errorCount
                    });
                }    

                //SALES
                foreach (string file in files)
                {
                    fileName = file;                    
                    Dictionary<string, string> fields;
                    rowNumber = 1;
                    using (StreamReader sr = new StreamReader(file, System.Text.Encoding.Default, true))
                    {
                        line = sr.ReadLine();

                        if (!line.Equals("Store Prov                           cMonth UPC                       Description                                                  Category  SubCat    Brand     Format    UOM  Sales                                    SKU_Quan                                 Sales_Amount                             Cost_Amount                              Margin                                   "))
                        {
                            errorCount++;
                            importerResults.Add(new ImporterResult
                            {
                                Category = ResultCategory.PreRequisites,
                                Description = string.Format("Row {0} in file {1} is incorrect. The headers are not in the correct format.", rowNumber, file),
                                ErrorType = ErrorType.Error,
                                Id = errorCount
                            });
                        }

                        line = sr.ReadLine();
                        rowNumber++;
                        if (!line.StartsWith("----") && !line.StartsWith("|---") && !line.StartsWith(" ---"))
                        {
                            errorCount++;
                            importerResults.Add(new ImporterResult
                            {
                                Category = ResultCategory.PreRequisites,
                                Description = string.Format("Row {0} in file {1} is incorrect.", rowNumber, file),
                                ErrorType = ErrorType.Error,
                                Id = errorCount
                            });
                        }

                        line = sr.ReadLine();
                        rowNumber++;
                        fields = ImporterHelper.GetFieldsFromFixedColumnLine(line, CanadaColumnsFormat1);
                        if (fields.Count != 15)
                        {
                            errorCount++;
                            importerResults.Add(new ImporterResult
                            {
                                Category = ResultCategory.PreRequisites,
                                Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                Description = Errors.ImporterErrorInvalidNumberOfFields,
                                ErrorType = ErrorType.Error,
                                Id = errorCount
                            });
                        }

                    }
                }
            }
            catch (Exception ex)
            {
                errorCount++;
                
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.PreRequisites,
                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }
        
        public IList<ImporterResult> ImportArticleMasterStep2()
        {
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            IList<ImporterResult> importerResults = new List<ImporterResult>();

            int errorCount = 0;
            string fileName = null;
            int? rowNumber = null;
            string line;

            try
            {
                // Limpio las tablas temporales [TempArticles].
                TempArticlesRepository.TruncateTable();

                string sourceDirectoryPath = ImporterHelper.GetArticleMasterFilePath(Country, rangeReview.Title);
                string[] files = Directory.GetFiles(sourceDirectoryPath);
                
                //Categories Cache
                List<CategoryRow> categories = CategoriesRepository.Select(); 
                CategoryRow category;
                 
                //Articles
                foreach (string file in files)
                {
                    fileName = file;
                    rowNumber = 0;

                    Dictionary<string, string> fields;                    
                    DataTable sourceTable = ImporterHelper.CreateDataTableFromGateway<TempArticleRow>();
                    
                    using (StreamReader sr = new StreamReader(file, System.Text.Encoding.Default, true))
                    {
                        while (!sr.EndOfStream)
                        {
                            line = sr.ReadLine();
                            rowNumber++;
                            if (!line.StartsWith("Store Prov ") && !line.StartsWith("-----") && !string.IsNullOrWhiteSpace(line))
                            {
                                fields = ImporterHelper.GetFieldsFromFixedColumnLine(line, CanadaColumnsFormat1);

                                if (fields != null && fields.Count == 15)
                                {
                                    category = categories.Where(cat => cat.Code == fields["SubCat"]).SingleOrDefault();

                                    DataRow datarow = sourceTable.NewRow();
                                    datarow["RangeReviewId"] = rangeReview.Id;
                                    datarow["CategoryId"] = category != null ? category.Id : 0;
                                    datarow["Code"] = fields["UPC"];
                                    datarow["Name"] = fields["Description"];
                                    datarow["EAN"] = fields["UPC"];
                                    datarow["UOM"] = fields["UOM"];
                                    datarow["Brand"] = fields["Brand"];
                                    datarow["Manufacturer"] = "";

                                    sourceTable.Rows.Add(datarow);
                                }
                                else
                                {
                                    errorCount++;

                                    if (fields == null)
                                    {
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                            Description = Errors.ImporterErrorParsingLine,
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }
                                    else
                                    {
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                            Description = Errors.ImporterErrorInvalidNumberOfFields,
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }
                                }
                            }
                        }
                    }

                    // new method: SQLBulkCopy:
                    TempArticlesRepository.BulkCopy(sourceTable);                    
                }

            }
            catch (Exception ex)
            {
                errorCount++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.TemporalTable,
                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }
        
        public IList<ImporterResult> ImportArticleMasterStep3()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();
            int errorCount = 0;

            try
            {
                //Elimino los duplicados de la tabla temporal.
                TempArticlesRepository.DeleteDuplicates();

                //Verifico si hay Articulos sin Codigo.
                List<TempArticleRow> articlesWithNoCode = TempArticlesRepository.Select(art => art.Code == null || art.Code == string.Empty);

                foreach (TempArticleRow art in articlesWithNoCode)
                {
                    errorCount++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Errors.TheArticleDoesntHaveCode, art.Name),
                        ErrorType = ErrorType.Error,
                        Id = errorCount
                    });

                    //TODO: Estoy eliminando los articulos que vienen sin codigo, pero deberia ofrecer alguna solución al usuario, alguna posibilidad de que él decida que hacer.
                    TempArticlesRepository.Delete(a => a.Id == art.Id);
                }

                //Verifico si hay Articulos sin Categoria.
                List<TempArticleRow> articlesWithNoCategory = TempArticlesRepository.Select(art => art.CategoryId == 0);

                foreach (TempArticleRow art in articlesWithNoCategory)
                {
                    errorCount++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Errors.TheArticleDoesNotHaveCategory, art.Name),
                        ErrorType = ErrorType.Error,
                        Id = errorCount
                    });

                    //TODO: Estoy eliminando los articulos que vienen sin category, pero deberia ofrecer alguna solución al usuario, alguna posibilidad de que él decida que hacer.
                    int artId = art.Id;
                    TempArticlesRepository.Delete(a => a.Id == artId);
                }
                
                
            }
            catch (Exception ex)
            {
                errorCount++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.Validation,
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportArticleMasterStep4()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();
            int count = 0;

            try
            {
                using (TransactionScope scope = new TransactionScope())
                {
                    //Hago Update del Maestro de Articulos.
                    ArticlesRepository.UpdateFromTempArticle(rangeReview.Id);

                    //Hago Insert del Maestro de Articulos.
                    ArticlesRepository.InsertFromTempArticles(rangeReview.Id);

                    scope.Complete();
                }

                //Borro las tablas temporales
                TempArticlesRepository.TruncateTable();

                //Muevo los archivos a la carpeta Old
                string sourceDirectoryPath = ImporterHelper.GetArticleMasterFilePath(Country, rangeReview.Title);
                ImporterHelper.MoveImportedFilesAndWriteLog(Country, sourceDirectoryPath);
            }
            catch (Exception ex)
            {
                count++;

                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.Updating,
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = count
                });
            }

            return importerResults;
        }


        public void ImportStoreMaster()
        {
            IList<ImporterResult> resultsStep1 = null;
            IList<ImporterResult> resultsStep2 = null;
            IList<ImporterResult> resultsStep3 = null;
            IList<ImporterResult> resultsStep4 = null;

            try
            {
                resultsStep1 = ImportStoreMasterStep1();
                WriteTextResults("Step1Results", resultsStep1);

                if (resultsStep1.Count == 0)
                {
                    resultsStep2 = ImportStoreMasterStep2();
                    WriteTextResults("Step2Results", resultsStep2);

                    resultsStep3 = ImportStoreMasterStep3();
                    WriteTextResults("Step3Results", resultsStep3);

                    resultsStep4 = ImportStoreMasterStep4();
                    WriteTextResults("Step4Results", resultsStep4);
                }
            }
            catch (Exception ex)
            {
                string resultsPath = ImporterHelper.GetSalesResultsFilesPath(Country, rangeReview.Title);
                string fullName = Path.Combine(resultsPath, "UnhandledErrors.txt");

                using (StreamWriter sw = new StreamWriter(fullName, true))
                {
                    sw.WriteLine("Exception: ", ex.Message);
                    sw.WriteLine(sw.NewLine);
                    sw.WriteLine("Stack Trace: ", ex.StackTrace);
                }
            }
        }

        public IList<ImporterResult> ImportStoreMasterStep1()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();

            string fileName = null;
            int? rowNumber = null;
            string line = null;
            int errorCount = 0;

            try
            {
                //Check Prerequisites
                //1. Chequeo que estén los archivos
                string sourceDirectoryPath = ImporterHelper.GetStoreMasterFilePath(Country, rangeReview.Title);
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                if (files.Length == 0)
                {
                    errorCount++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.PreRequisites,
                        Description = "There are no Sales files to import in the server directory.",
                        ErrorType = ErrorType.Error,
                        Id = errorCount
                    });
                }
                
                //SALES
                foreach (string file in files)
                {
                    fileName = file;

                    Dictionary<string, string> fields;
                    rowNumber = 1;
                    using (StreamReader sr = new StreamReader(file, System.Text.Encoding.Default, true))
                    {
                        line = sr.ReadLine();

                        if (!line.Equals("Store Prov                           cMonth UPC                       Description                                                  Category  SubCat    Brand     Format    UOM  Sales                                    SKU_Quan                                 Sales_Amount                             Cost_Amount                              Margin                                   "))
                        {
                            errorCount++;
                            importerResults.Add(new ImporterResult
                            {
                                Category = ResultCategory.PreRequisites,
                                Description = string.Format("Row {0} in file {1} is incorrect. The headers are not in the correct format.", rowNumber, file),
                                ErrorType = ErrorType.Error,
                                Id = errorCount
                            });
                        }

                        line = sr.ReadLine();
                        rowNumber++;
                        if (!line.StartsWith("----") && !line.StartsWith("|---") && !line.StartsWith(" ---"))
                        {
                            errorCount++;
                            importerResults.Add(new ImporterResult
                            {
                                Category = ResultCategory.PreRequisites,
                                Description = string.Format("Row {0} in file {1} is incorrect.", rowNumber, file),
                                ErrorType = ErrorType.Error,
                                Id = errorCount
                            });
                        }

                        //TODO: Chequear algun valor de la primer linea que me asegure que el file es el del pais correcto.
                        line = sr.ReadLine();
                        rowNumber++;
                        fields = ImporterHelper.GetFieldsFromFixedColumnLine(line, CanadaColumnsFormat1);
                        if (fields.Count != 15)
                        {
                            errorCount++;
                            importerResults.Add(new ImporterResult
                            {
                                Category = ResultCategory.PreRequisites,
                                Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                Description = Errors.ImporterErrorInvalidNumberOfFields,
                                ErrorType = ErrorType.Error,
                                Id = errorCount
                            });
                        }

                    }
                }
            }
            catch (Exception ex)
            {
                errorCount++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.PreRequisites,
                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportStoreMasterStep2()
        {
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            IList<ImporterResult> importerResults = new List<ImporterResult>();

            string fileName = null;
            int? rowNumber = null;
            string line = null;
            int errorCount = 0;
            
            try
            {
                // Limpio las tablas temporales [TempStores].
                TempStoresRepository.TruncateTable();

                string sourceDirectoryPath = ImporterHelper.GetStoreMasterFilePath(Country, rangeReview.Title);
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                //Stores
                foreach (string file in files)
                {
                    fileName = file;
                    rowNumber = 0;

                    Dictionary<string, string> fields;

                    DataTable sourceTable = ImporterHelper.CreateDataTableFromGateway<TempStoreRow>();

                    using (StreamReader sr = new StreamReader(file, System.Text.Encoding.Default, true))
                    {
                        while (!sr.EndOfStream)
                        {
                            line = sr.ReadLine();
                            rowNumber++;
                            if (!line.StartsWith("Store Prov ") && !line.StartsWith("-----") && !string.IsNullOrWhiteSpace(line))
                            {
                                fields = ImporterHelper.GetFieldsFromFixedColumnLine(line, CanadaColumnsFormat1);


                                if (fields != null && fields.Count == 15)
                                {                                    
                                    DataRow datarow = sourceTable.NewRow();
                                    datarow["CountryId"] = Convert.ToInt16(Country.Id);
                                    datarow["Code"] = fields["Store"];
                                    datarow["Name"] = fields["Store"];
                                    datarow["Region"] = fields["Prov"];

                                    sourceTable.Rows.Add(datarow);
                                }
                                else
                                {
                                    errorCount++;

                                    if (fields == null)
                                    {
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                            Description = Errors.ImporterErrorParsingLine,
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }
                                    else
                                    {
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                            Description = Errors.ImporterErrorInvalidNumberOfFields,
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }
                                }                                
                            }
                        }
                    }

                    // new method: SQLBulkCopy:
                    TempStoresRepository.BulkCopy(sourceTable);
                }

            }
            catch (Exception ex)
            {
                errorCount++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.TemporalTable,
                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportStoreMasterStep3()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();
            int count = 0;
            try
            {
                //borro los duplicados.
                TempStoresRepository.DeleteDuplicates();

                //Verifico si hay tiendas sin Codigo o Nombre.
                List<TempStoreRow> storesWithoutCode = TempStoresRepository.Select(store => store.Code == null || store.Code == string.Empty);

                foreach (TempStoreRow store in storesWithoutCode)
                {
                    count++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Errors.StoreDoesntHaveCode, store.Name),
                        ErrorType = ErrorType.Error,
                        Id = count
                    });

                    //TODO: Estoy eliminando los stores que vienen sin codigo, pero deberia ofrecer alguna solución al usuario, alguna posibilidad de que él decida que hacer.
                    TempStoresRepository.Delete(st => st.Id == store.Id);
                }

            }
            catch (Exception ex)
            {
                count++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.Validation,
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = count
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportStoreMasterStep4()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();
            int count = 0;

            try
            {
                using (TransactionScope scope = new TransactionScope())
                {
                    //Hago Update de Maestro de Tiendas
                    StoresRepository.UpdateFromTempStores(Country.Id);

                    //Hago Insert de Maestro Tiendas
                    StoresRepository.InsertFromTempStores(Country.Id, rangeReview.Id);

                    scope.Complete();
                }

                //Borro las tablas temporales
                TempStoresRepository.TruncateTable();

                //Muevo Archivos importados a la carpeta old y Loggeo la importacion.
                string sourceDirectoryPath = ImporterHelper.GetStoreMasterFilePath(Country, rangeReview.Title);
                ImporterHelper.MoveImportedFilesAndWriteLog(Country, sourceDirectoryPath);
            }
            catch (Exception ex)
            {
                count++;

                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.Updating,
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = count
                });
            }

            return importerResults;
        }


        public void ImportArticleSales()
        {
            IList<ImporterResult> resultsStep1 = null;
            IList<ImporterResult> resultsStep2 = null;
            IList<ImporterResult> resultsStep3 = null;
            IList<ImporterResult> resultsStep4 = null;

            try
            {
                resultsStep1 = ImportArticleSalesStep1();
                WriteTextResults("Step1Results", resultsStep1);

                if (resultsStep1.Count == 0)
                {
                    resultsStep2 = ImportArticleSalesStep2();
                    WriteTextResults("Step2Results", resultsStep2);

                    resultsStep3 = ImportArticleSalesStep3();
                    WriteTextResults("Step3Results", resultsStep3);

                    resultsStep4 = ImportArticleSalesStep4();
                    WriteTextResults("Step4Results", resultsStep4);
                }
            }
            catch (Exception ex)
            {
                string resultsPath = ImporterHelper.GetSalesResultsFilesPath(Country, rangeReview.Title);
                string fullName = Path.Combine(resultsPath, "UnhandledErrors.txt");

                using (StreamWriter sw = new StreamWriter(fullName, true))
                {
                    sw.WriteLine("Exception: ", ex.Message);
                    sw.WriteLine(sw.NewLine);
                    sw.WriteLine("Stack Trace: ", ex.StackTrace);
                }
            }
        }

        public IList<ImporterResult> ImportArticleSalesStep1()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();
            
            string fileName = null;
            int? rowNumber = null;
            string line = null;
            int errorCount = 0;

            try
            {
                //Check Prerequisites
                //1. Chequeo que estén los archivos
                string sourceDirectoryPath = ImporterHelper.GetStoreMasterFilePath(Country, rangeReview.Title);
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                if (files.Length == 0)
                {
                    errorCount++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.PreRequisites,
                        Description = "There are no Sales files to import in the server directory.",
                        ErrorType = ErrorType.Error,
                        Id = errorCount
                    });
                }

                //SALES
                foreach (string file in files)
                {
                    fileName = file;

                    Dictionary<string, string> fields;
                    rowNumber = 1;
                    using (StreamReader sr = new StreamReader(file, System.Text.Encoding.Default, true))
                    {
                        line = sr.ReadLine();

                        if (!line.Equals("Store Prov                           cMonth UPC                       Description                                                  Category  SubCat    Brand     Format    UOM  Sales                                    SKU_Quan                                 Sales_Amount                             Cost_Amount                              Margin                                   "))
                        {
                            errorCount++;
                            importerResults.Add(new ImporterResult
                            {
                                Category = ResultCategory.PreRequisites,
                                Description = string.Format("Row {0} in file {1} is incorrect. The headers are not in the correct format.", rowNumber, file),
                                ErrorType = ErrorType.Error,
                                Id = errorCount
                            });
                        }

                        line = sr.ReadLine();
                        rowNumber++;
                        if (!line.StartsWith("----") && !line.StartsWith("|---") && !line.StartsWith(" ---"))
                        {
                            errorCount++;
                            importerResults.Add(new ImporterResult
                            {
                                Category = ResultCategory.PreRequisites,
                                Description = string.Format("Row {0} in file {1} is incorrect.", rowNumber, file),
                                ErrorType = ErrorType.Error,
                                Id = errorCount
                            });
                        }

                        //TODO: Chequear algun valor de la primer linea que me asegure que el file es el del pais correcto.
                        line = sr.ReadLine();
                        rowNumber++;
                        fields = ImporterHelper.GetFieldsFromFixedColumnLine(line, CanadaColumnsFormat1);
                        if (fields.Count != 15)
                        {
                            errorCount++;
                            importerResults.Add(new ImporterResult
                            {
                                Category = ResultCategory.PreRequisites,
                                Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                Description = Errors.ImporterErrorInvalidNumberOfFields,
                                ErrorType = ErrorType.Error,
                                Id = errorCount
                            });
                        }

                    }
                }
            }
            catch (Exception ex)
            {
                errorCount++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.PreRequisites,
                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportArticleSalesStep2()
        {
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            IList<ImporterResult> importerResults = new List<ImporterResult>();

            string fileName = null;
            int? rowNumber = null;
            string line = null;
            int errorCount = 0;
            
            try
            {
                // Limpio las tablas temporales [TempArticleSales].
                TempArticleSalesRepository.TruncateTable();

                string sourceDirectoryPath = ImporterHelper.GetSalesFilesPath(Country, rangeReview.Title);
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                //Cacheo en memoria los articles y los stores del pais.
                List<ArticleRow> Articles = ArticlesRepository.Select(art => art.RangeReviewId == rangeReview.Id);
                List<StoreRow> Stores = StoresRepository.Select(st => st.CountryId == Country.Id);
                StoreRow store = null;
                ArticleRow article = null;
                //Sales
                foreach (string file in files)
                {
                    fileName = file;
                    rowNumber = 0;

                    Dictionary<string, string> fields;
                    DataTable sourceTable = ImporterHelper.CreateDataTableFromGateway<TempArticleSaleRow>();

                    using (StreamReader sr = new StreamReader(file, System.Text.Encoding.Default, true))
                    {
                        //variables
                        string period;
                        DateTime? date = null;
                        int? year = null;
                        int? month = null;
                        decimal? units = null;
                        decimal? sales = null;
                        decimal? grossMargin = null;
                        decimal? grossSales = null;
                        decimal? price = null;

                        while (!sr.EndOfStream)
                        {
                            line = sr.ReadLine();
                            rowNumber++;
                            if (!line.StartsWith("Store Prov ") && !line.StartsWith("-----") && !string.IsNullOrWhiteSpace(line))
                            {
                                fields = ImporterHelper.GetFieldsFromFixedColumnLine(line, CanadaColumnsFormat1);

                                if (fields != null && fields.Count == 15)
                                {
                                    //Chequeo que el store no sea nulo y que exista en el maestro de tiendas.
                                    string storeCode = fields["Store"];
                                    if (!string.IsNullOrWhiteSpace(storeCode))
                                    {
                                        store = Stores.Where(st => st.Code == storeCode).SingleOrDefault();
                                        if (store == null)
                                        {
                                            errorCount++;
                                            importerResults.Add(new ImporterResult
                                            {
                                                Category = ResultCategory.TemporalTable,
                                                Description = string.Format(Errors.StoreCodeNotFoundInMaster, fields["Store"], rowNumber),
                                                ErrorType = ErrorType.Error,
                                                Id = errorCount
                                            });
                                        }
                                    }
                                    else
                                    {
                                        errorCount++;
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Description = string.Format(Errors.StoreCodeCantBeNull, rowNumber),
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }

                                    //Chequeo que el Article no sea nulo y que exista en el maestro de Articulos.
                                    string articleEAN = fields["UPC"];
                                    if (!string.IsNullOrWhiteSpace(articleEAN))
                                    {
                                        article = Articles.Where(art => art.EAN == articleEAN).SingleOrDefault();
                                        if (article == null)
                                        {
                                            errorCount++;
                                            importerResults.Add(new ImporterResult
                                            {
                                                Category = ResultCategory.TemporalTable,
                                                Description = string.Format(Errors.ArticleEanNotFoundInMaster, fields["UPC"], rowNumber),
                                                ErrorType = ErrorType.Error,
                                                Id = errorCount
                                            });
                                        }
                                    }
                                    else
                                    {
                                        errorCount++;
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Description = string.Format(Errors.ArticleEANCantBeNull, rowNumber),
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }

                                    try
                                    {
                                        //Parseo la fecha
                                        period = fields["cMonth"];
                                        year = int.Parse(period.Substring(0, 4));
                                        month = int.Parse(period.Substring(4, 2));
                                        date = new DateTime(year.Value, month.Value, 1);

                                        units = decimal.Parse(fields["SKU_Quan"]);
                                        sales = decimal.Parse(fields["Sales"]);
                                        grossMargin = decimal.Parse(fields["Margin"]);
                                        grossSales = decimal.Parse(fields["Sales_Amount"]);
                                        price = units > 0 ? grossSales / units : 0;

                                        //Agrego un registro de venta al DataTable.
                                        if (article != null && store != null)
                                        {
                                            DataRow datarow = sourceTable.NewRow();
                                            datarow["StoreId"] = store.Id;
                                            datarow["ArticleId"] = article.Id;
                                            datarow["Date"] = date;
                                            datarow["Units"] = units;
                                            datarow["Sales"] = sales;
                                            datarow["GM"] = grossMargin;
                                            datarow["GrossSales"] = grossSales;
                                            datarow["Price"] = price;

                                            sourceTable.Rows.Add(datarow);
                                        }
                                    }
                                    catch (Exception ex)
                                    {
                                        errorCount++;
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                            Description = ex.Message,
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }
                                }
                                else
                                {
                                    errorCount++;

                                    if (fields == null)
                                    {
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                            Description = Errors.ImporterErrorParsingLine,
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }
                                    else
                                    {
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                            Description = Errors.ImporterErrorInvalidNumberOfFields,
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }
                                }                   
                            }
                        }
                    }

                    // new method: SQLBulkCopy:
                    TempArticleSalesRepository.BulkCopy(sourceTable);
                }

            }
            catch (Exception ex)
            {
                errorCount++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.TemporalTable,
                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportArticleSalesStep3()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();
            int count = 0;
            try
            {
                //Verifico si hay registros con margenes negativos.
                List<TempArticleSaleRow> negativeMargins = TempArticleSalesRepository.Select(sale => sale.GM < 0);
                
                //TODO: Agregar aqui todas las validaciones necesarias para el Maestro de Tiendas
            }
            catch (Exception ex)
            {
                count++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.Validation,
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = count
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportArticleSalesStep4()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();
            int count = 0;

            try
            {
                using (TransactionScope scope = new TransactionScope())
                {
                    //Hago Update de las ventas del pais
                    ArticleSalesRepository.UpdateFromTempArticleSales();

                    //Hago Insert de las ventas del pais
                    ArticleSalesRepository.InsertFromTempArticleSales();

                    scope.Complete();
                }

                //Borro las tablas temporales
                TempArticleSalesRepository.TruncateTable();

                //Muevo Archivos importados a la carpeta old y Loggeo la importacion.
                string sourceDirectoryPath = ImporterHelper.GetStoreMasterFilePath(Country, rangeReview.Title);
                ImporterHelper.MoveImportedFilesAndWriteLog(Country, sourceDirectoryPath);
            }
            catch (Exception ex)
            {
                count++;

                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.Updating,
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = count
                });
            }

            return importerResults;
        }


        public void WriteTextResults(string fileName, IList<ImporterResult> results)
        {
            string resultsPath = ImporterHelper.GetSalesResultsFilesPath(Country, rangeReview.Title);
            string fullName = Path.Combine(resultsPath, fileName + ".txt");

            using (StreamWriter sw = new StreamWriter(fullName, false))
            {
                if (results.Count > 0)
                {
                    foreach (ImporterResult res in results)
                    {
                        sw.WriteLine(res.CreateErrorMessageSingleLineFormated);
                    }
                }
                else
                {
                    sw.WriteLine("Success going through this step!");
                }
            }               
        }

    }
}
